#-*- coding : utf-8 -*-
"""
This script is for iniciate PPCaseInfo table in Mysql with excel table
"""
from xlrd import open_workbook ,cellname
import MySQLdb

# Database Connection Info
db_host = "localhost"
db_user = "root"
db_passwd = "milanoo@)08"
db_database = "accountm"

# Assign path to Excel file
file_to_import = 'E:\Python\PayPalComplain.xls'

# Assign column count
# This can be detected but to get a subset
# of the existing column cells I assign it manually
column_count=9

# open the entire workbook
# you can work with multiple page workbooks
book = open_workbook(file_to_import)

# we are only using the first sheet
sheet = book.sheet_by_index(0)

# Why not?
print "Workbook sheet name:%s" % sheet.name
print "Number of rows in sheet: %s" % sheet.nrows

# get a connection handle to MySQL
conn = MySQLdb.connect (host=db_host, user=db_user, passwd=db_passwd, db=db_database)

# get the cursor
cursor = conn.cursor()

# since we are using placeholders, we really only need to assign the query string sans values once, outside the loop
query = """INSERT INTO pp_case_ppcaseinfo (caseID, orderNo, paymentAmount, paymentCurrency,transactionID, caseStartDate,paypalCaseDeclaration, paypalCaseStatus, paypalCaseWindow) VALUES (%s, %s, %s, %s, %s, %s,%s,%s,%s)"""

# iterate through ieach row
for row_index in range(sheet.nrows):

    m_list = []
    if row_index == 0:
        continue
    # we could assign the values directly in the values format string but this is easier to read
    for k in range(9):
        m_cell = sheet.cell(row_index,k).value
        try :
            m_cell = m_cell.decode('gb2312').encode('utf8')
        except :
            pass
        m_list.append(m_cell)
    print m_list
    if row_index > 2:
        break
"""
    row_num     = row_index
    caseID =   m_list[0]
    orderNo  =     m_list[1]
    paymentAmount = m_list[2]
    paymentCurrency = m_list[3]
    transactionID = m_list[4]
    caseStartDate = m_list[5]
    paypalCaseDeclaration =  m_list[6]
    paypalCaseStatus = m_list[7]
    paypalCaseWindow = m_list[8]

    # the row contents are different every time so we put this in the loop
    values = (caseID, orderNo, paymentAmount, paymentCurrency,transactionID, caseStartDate,paypalCaseDeclaration, paypalCaseStatus, paypalCaseWindow)

    res = cursor.execute(query, values)

        # uncomment the next line to troubleshoot and see what's going on
        # print"row:%s data %s - %s - %s - %s - %s - %s" % (row_num, issue_num, company, product, reviewers, contact, review_text)

# close cursor
cursor.close()

# We are using an InnoDB tables so we need to commit the transaction
conn.commit()

#close connection
"""
conn.close()